<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Languages in Hattrick</title>
</head>
<body>
<?php

include_once('database.inc.php');
include_once('track.inc.php');
include_once('functions.inc.php');
include_once('dropdowns.inc.php');

$updates = array_reverse(array_merge(
	array(
	        "2005-06-16",
	        "2005-07-08",
	        "2005-07-28",
	        "2005-08-24",
	        "2005-09-08",
	        "2005-09-29",
	        "2005-10-19",
	        "2005-11-09",
	        "2005-12-01",
	        "2005-12-23",
	        "2006-01-13",
	        "2006-02-03",
	        "2006-02-20",
	        "2006-03-13",
	        "2006-04-03"
	),
	getAllUpdates()
));

$dbh = connect_db();

mysql_query("SET NAMES latin1", $dbh) or die (mysql_error());

$action = -1;
if (isset($_GET['action']) && is_numeric($_GET['action'])) $action = $_GET['action'];

$language = 0;
if (isset($_GET['la']) && is_numeric($_GET['la'])) $language = $_GET['la'];
$languages = getAsocArr("languages","LanguageID","Language",$dbh);

$leagueid = 0;
if (isset($_GET['leagueid']) && is_numeric($_GET['leagueid'])) $leagueid = $_GET['leagueid'];
$leaguenames = getAsocArr("leagues_daily","LeagueID","LeagueName",$dbh);

if (!$query = mysql_query("
        SELECT zonename
        FROM `leagues_daily`
        WHERE leagueid != 255
        GROUP BY zonename", $dbh)
) die(mysql_error());
$zonelist = array('any zone');
while ($row = mysql_fetch_array($query)) $zonelist[] = $row[0];

$zoneid = 0;
$wherezone = "";
if (isset($_GET['zoneid']) && $_GET['zoneid'] > 0 && $leagueid == 0 && is_numeric($_GET['zoneid'])) {
        $zoneid = $_GET['zoneid'];
	if (!$query = mysql_query(
		"SELECT leagueid
		FROM leagues_daily
		WHERE zonename = '{$zonelist[$zoneid]}'",
		$dbh
	)) die (mysql_error());
	$leaguesinzone = array();
	while ($row = mysql_fetch_array($query)) {
		$leaguesinzone[] = $row['leagueid'];
	}
	for ($i = 0; $i < count($leaguesinzone); $i++) {
		if ($i == 0) $wherezone = "(leagueid = {$leaguesinzone[$i]}";
		else $wherezone .= " OR leagueid = {$leaguesinzone[$i]}";
	}
	$wherezone .= ")";
}

$update = 0; //lastupdate
if (isset($_GET['update']) && is_numeric($_GET['update'])) $update = $_GET['update'];
$updatedate = $updates[$update];

$sup = 2; //don't care
if (isset($_GET['filter']) && is_numeric($_GET['filter'])) $sup = $_GET['filter'];
$filters = array(
	"2" => "no filter used",
	"0" => "only supporters",
	"1" => "only non-supporters"
);

?>
<form name="maptrick">
<?php printzonedropdown($zoneid, $dbh); ?>
<?php printleaguedropdown($leagueid, $zoneid, "all countries", $dbh); ?>
<br />
<?php printgenericdropdown("la", $language, "all languages", $languages); ?> 
<?php printgenericdropdown("filter", $sup, "", $filters); ?>
<br />
<?php printgenericdropdown("update", $update, "", $updates); ?>
<input type="submit" value="go!" />
</form>
<?
if ($action == 0) { //debug to find new languages in last update
	$query = mysql_query(
		"SELECT t.*, l.languageid missinglang
		FROM `teams_$updatedate` t LEFT JOIN languages l ON l.languageid = t.languageid
		WHERE userid > 0 AND t.languageid > 0
		GROUP BY t.languageid
		HAVING missinglang IS NULL", $dbh
	) or die ("error: " . mysql_error() . "\n");
	if (mysql_num_rows($query) > 0) {
		echo "Undocumented languages as of $updatedate update - <b>please report to <a href=\"mailto:lhoek@science.uva.nl\">lhoek@science.uva.nl</a></b>!<br /><br />\n";
		echo "<table border=\"1\">\n";
		echo "<tr><th>ID<th>Example TeamID (first click will give timeout)\n";
		while ($row = mysql_fetch_array($query)) {
			echo "<tr><td>{$row['LanguageID']}<td><a href=\"http://www.hattrick.org/Common/teamDetails.asp?teamid={$row['TeamID']}\">{$row['TeamID']}</a>\n";
		}
		echo "</table>";
	} else {
		echo "No undocumented languages found.<br />\n";
	}
} else if ($action == 1) { //debug to find unused languages in last update
	if (!$query = mysql_query("SELECT l.LanguageID, Language, userid, COUNT(*) num FROM languages l LEFT JOIN `teams_$updatedate` t ON l.languageid = t.languageid GROUP BY l.languageid HAVING userid IS NULL ORDER BY l.LanguageID ASC", $dbh)) die (mysql_error());
	if (mysql_num_rows($query) > 0) {
		echo "Unused languages as of $updatedate update - <b>please report to <a href=\"mailto:lhoek@science.uva.nl\">lhoek@science.uva.nl</a></b>!<br /><br />\n";
		echo "<table border=\"1\">\n";
		echo "<tr><th>ID<th>Language\n";
		while ($row = mysql_fetch_array($query)) {
			echo "<tr><td>{$row['LanguageID']}<td>{$row['Language']}\n";
		}
		echo "</table>";
	} else {
		echo "No unused languages found.<br />\n";
	}
} else { //normal functionality
	if ($language > 0) {
		// This will be shown when a language is selected
		if ($leagueid > 0) {
			// This will be shown if a specific league is selected, with a language.
			echo "Historical use of <a href=\"languages.php?la=$language&filter=$sup\">{$languages[$language]}</a> in <a href=\"languages.php?leagueid=$leagueid&filter=$sup\">{$leaguenames[$leagueid]}</a>:<br /><br />\n";
			echo "<table border=\"1\">";
			echo "<tr><th>Date<th>Users<th>% of all {$leaguenames[$leagueid]}\n";
			foreach($updates as $update) {
				$query = mysql_query(
					"SELECT COUNT(*) num FROM `teams_$update`
					WHERE leagueid = $leagueid
					AND userid > 0", $dbh
				) or die (mysql_error());
				$row = mysql_fetch_object($query);
				$numthisupdate = $row->num;
				$query = mysql_query(
					"SELECT COUNT(*) num FROM `teams_$update`
					WHERE languageid = $language
					AND hassupporter != $sup
					AND leagueid = $leagueid", $dbh
				) or die (mysql_error());
				$row = mysql_fetch_object($query);
				echo "<tr><td>$update<td>{$row->num}<td>" . round(100*($row->num / $numthisupdate),2) . "\n";
				ob_flush();
				flush();
			}
			echo "</table>\n";
		} else if ($zoneid > 0) {
			//This will be shown if a zone is selected (but no league), with a language
			echo "Historical use of <a href=\"languages.php?la=$language&filter=$sup\">{$languages[$language]}</a> in <a href=\"languages.php?zoneid=$zoneid&filter=$sup\">{$zonelist[$zoneid]}</a>:<br /><br />\n";
			echo "<table border=\"1\">";
			echo "<tr><th>Date<th>Users<th>% of all {$zonelist[$zoneid]}\n";
			foreach($updates as $update) {
				$query = mysql_query(
					"SELECT COUNT(*) num FROM `teams_$update`
					WHERE $wherezone
					AND userid > 0", $dbh				
				) or die (mysql_error());
				$row = mysql_fetch_object($query);
				$numthisupdate = $row->num;
				$query = mysql_query(
					"SELECT COUNT(*) num FROM `teams_$update`
					WHERE languageid = $language
					AND hassupporter != $sup
					AND $wherezone", $dbh
				) or die (mysql_error());
				$row = mysql_fetch_object($query);
				echo "<tr><td>$update<td>{$row->num}<td>" . round(100*($row->num / $numthisupdate),2) . "\n";
				ob_flush();
				flush();
			}
			echo "</table>\n";
		} else {
			echo "A list of countries with users who have {$languages[$language]} set as their language";
			if ($sup != 2) echo ", showing {$filters[$sup]}";
			echo "<br />\n";
			echo "<table>\n";
			echo "<tr><th>League<th>Number of users<th>% of users with {$languages[$language]}\n";
			$querytext = 
				"SELECT leagueid, COUNT(*) num, 100*(COUNT(*) / total) perc
				FROM `teams_$updatedate`, (
					SELECT COUNT(*) total
					FROM `teams_$updatedate`
					WHERE languageid = $language
					AND userid > 0
				) subselect
				WHERE userid > 0
				AND hassupporter != $sup
				AND languageid = $language
				GROUP BY leagueid
				ORDER BY num DESC";
			if (!$query = mysql_query($querytext, $dbh)) die ($querytext . mysql_error());
			while ($row = mysql_fetch_array($query)) {
				echo "<tr><td><a href=\"languages.php?leagueid={$row['leagueid']}&la=$language&filter=$sup\">{$leaguenames[$row['leagueid']]}</a><td>{$row['num']}<td>{$row['perc']}\n";
			}
			echo "</table>\n";
		}
	} else { //This will be shown if no language is selected.
		if ($leagueid > 0) {
			echo "A list of all languages used in {$leaguenames[$leagueid]} as of $updatedate";
			if ($sup != 2) echo ", showing {$filters[$sup]}";
			echo ".<br />\n";
			$querytext =
				"SELECT Language, LeagueID, l.LanguageID, count(*) num, filtered_total, 100*(COUNT(*) / total) perc, 100*(COUNT(*) / filtered_total) fperc
				FROM languages l, `teams_$updatedate` t, (
					SELECT COUNT(*) total
					FROM `teams_$updatedate`
					WHERE leagueid = $leagueid
					AND userid > 0
				) subselect_total,
				(
					SELECT COUNT(*) filtered_total
					FROM `teams_$updatedate`
					WHERE leagueid = $leagueid
					AND userid > 0
					AND hassupporter != $sup
				) subselect_filtered
				WHERE l.languageid = t.languageid
				AND leagueid = $leagueid
				AND userid > 0
				AND hassupporter != $sup
				GROUP BY l.languageid
				ORDER BY num DESC";
			if (!$query = mysql_query($querytext, $dbh)) die ($querytext . mysql_error());
			echo "<table border=\"1\">\n";
			echo "<tr><th>Language (ID)<th>Number of users<th>% of all {$row['num']} {$leaguenames[$leagueid]} users";
			if ($sup != 2) {
				if ($sup == 0) echo "<th>% of all {$row['filtered_total']} supporters in {$leaguenames[$leagueid]}";
				if ($sup == 1) echo "<th>% of all {$row['filtered_total']} non-supporters in {$leaguenames[$leagueid]}";
			}
			echo "\n";
				
			while ($row = mysql_fetch_array($query)) {
				echo "<tr><td><a href=\"languages.php?leagueid={$row['LeagueID']}&la={$row['LanguageID']}&filter=$sup\">{$languages[$row['LanguageID']]}</a> ({$row['LanguageID']})<td>{$row['num']}<td>{$row['perc']}";
				if ($sup != 2) echo "<td>{$row['fperc']}";
				echo "\n";
			}
			echo "</table>";
		} else if ($zoneid > 0) {
			// This will be shown if a zone is selected (but no league or language).
			echo "A list of all languages used in {$zonelist[$zoneid]} as of $updatedate";
			if ($sup != 2) echo ", showing {$filters[$sup]}";
			echo ".<br />\n";
			$querytext =
				"SELECT Language, l.LanguageID, count(*) num, filtered_total, 100*(COUNT(*) / total) perc, 100*(COUNT(*) / filtered_total) fperc
				FROM languages l, `teams_$updatedate` t, (
					SELECT COUNT(*) total
					FROM `teams_$updatedate`
					WHERE $wherezone
					AND userid > 0
				) subselect_total,
				(
					SELECT COUNT(*) filtered_total
					FROM `teams_$updatedate`
					WHERE $wherezone
					AND userid > 0
					AND hassupporter != $sup
				) subselect_filtered
				WHERE l.languageid = t.languageid
				AND $wherezone
				AND userid > 0
				AND hassupporter != $sup
				GROUP BY l.languageid
				ORDER BY num DESC";
			if (!$query = mysql_query($querytext, $dbh)) die ($querytext . mysql_error());
			echo "<table border=\"1\">\n";
			echo "<tr><th>Language (ID)<th>Number of users<th>% of all {$row['num']} {$zonelist[$zoneid]} users";
			if ($sup != 2) {
				if ($sup == 0) echo "<th>% of all {$row['filtered_total']} supporters in {$zonelist[$zoneid]}";
				if ($sup == 1) echo "<th>% of all {$row['filtered_total']} non-supporters in {$zonelist[$zoneid]}";
			}
			echo "\n";
			while ($row = mysql_fetch_array($query)) {
				echo "<tr><td><a href=\"languages.php?zoneid=$zoneid&la={$row['LanguageID']}&filter=$sup\">{$languages[$row['LanguageID']]}</a> ({$row['LanguageID']})<td>{$row['num']}<td>{$row['perc']}";
				if ($sup != 2) echo "<td>{$row['fperc']}";
				echo "\n";
			}
			echo "</table>";
		} else {
			echo "A list of all languages used world-wide as of $updatedate";
			if ($sup != 2) echo ", showing {$filters[$sup]}";
			echo ".<br />\n";
			$querytext =
				"SELECT Language, l.LanguageID, count(*) num, filtered_total, 100*(COUNT(*) / total) perc, 100*(COUNT(*) / filtered_total) fperc
				FROM languages l, `teams_$updatedate` t, (
					SELECT COUNT(*) total
					FROM `teams_$updatedate`
					WHERE userid > 0
				) subselect_total,
				(
					SELECT COUNT(*) filtered_total
					FROM `teams_$updatedate`
					WHERE userid > 0
					AND hassupporter != $sup
				) subselect_filtered
				WHERE l.languageid = t.languageid
				AND userid > 0
				AND hassupporter != $sup
				GROUP BY l.languageid
				ORDER BY num DESC";
			if (!$query = mysql_query($querytext, $dbh)) die ($querytext . mysql_error());
			echo "<table border=\"1\">\n";
			echo "<tr><th>Language (ID)<th>Number of users<th>% of all {$row['num']} users";
			if ($sup != 2) {
				if ($sup == 0) echo "<th>% of all {$row['filtered_total']} supporters";
				if ($sup == 1) echo "<th>% of all {$row['filtered_total']} non-supporters";
			}
			echo "</tr>\n";
			while ($row = mysql_fetch_array($query)) {
				echo "<tr><td><a href=\"languages.php?la={$row['LanguageID']}&filter=$sup\">{$languages[$row['LanguageID']]}</a> ({$row['LanguageID']})<td>{$row['num']}<td>{$row['perc']}";
				if ($sup != 2) echo "<td>{$row['fperc']}";
				echo "\n";
			}
			echo "</table>";
		}
	}
}
urchin();
?>
<br /><small><a href="languages.php?action=0">Check for undocumented languages</a></small>
<br /><small><a href="languages.php?action=1">Check for unused languages</a></small>
</body>
</html>
